{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 作业7 空间函数和触发器"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "姓名：郑昱笙"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "学号：3180102760"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**作业目的：**了解PostGIS空间函数的实现，熟悉PostgreSQL的PL/pgSQL语言和函数创建方法，掌握OGC SFA 标准对geometry对象定义的30个方法，熟悉视图的可更新标准，掌握使用instead of触发器实现视图更新，熟悉道路匹配、引导点识别、引导角度等路径导航模块，掌握PostgreSQL的函数和触发器在地理空间数据中的应用。\n",
    "\n",
    "**注意事项：**\n",
    "* SQL语句的错误输出为乱码时，修改SET client_encoding = 'GBK';或SET client_encoding = 'UTF-8';，重新连接数据库\n",
    "* Jupyter Notebook对SQL语句的错误提示较弱，可以先在pgAdmin 4上执行，查看详细的错误信息\n",
    "* PostgreSQL的函数很难调试，大部分通过**Raise Notice 'XXXX % %', 10, 'YYY';**输出消息进行调试。建议先在pgAdmin 4上创建函数，进行简单测试，可在消息窗口查看Raise Notice的消息输出，判断代码是否按照预期的那样执行   \n",
    "* 在列表（如linegroupA = [...]）中，增加几何要素进行测试，但不要修改其他Python测试代码\n",
    "* 作业7总分60分，作业考察的题目后面标了具体分数，可以相互讨论思路，作业抄袭或雷同都要扣分\n",
    "* **学号.jpg和作业7\\_学号\\_姓名.ipynb**替换其中的学号和姓名，包含执行结果，和**jsonData文件夹**一起压缩为__作业7\\_学号\\_姓名.rar/zip__，**不要包含数据文件**，提交到学在浙大，作业7截止日期**2020.5.31**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"http://www.opengeospatial.org/docs/is\" target=\"_blank\">Open Geospatial Consortium</a>的Simple Feature Access标准包含两个部分Part 1 <a href=\"http://portal.opengeospatial.org/files/?artifact_id=25355\" target=\"_blank\">Common architecture</a>和Part 2 <a href=\"http://portal.opengeospatial.org/files/?artifact_id=25354\" target=\"_blank\">SQL option</a>两部分，给出了地理空间几何类型及其SQL实现规范，建议阅读参考。Geometry类（注意和Geography类的区别）给出了以下3大类几何对象方法（作业5已经实现了Distance和Winthin函数）。\n",
    "\n",
    "**常规方法**\n",
    "\n",
    "<table>\n",
    "    <tr><td>1</td><td>Dimension() : Integer</td><td>用于获取几何对象的几何维数</td></tr>\n",
    "    <tr><td>2</td><td>CoordinateDimension() : Integer</td><td>用于获取几何对象的坐标维数</td></tr>\n",
    "    <tr><td>3</td><td>GeometryType() : String</td><td>用于获取几何的数据类型，如点、线、面等</td></tr>\n",
    "    <tr><td>4</td><td>SRID() : Integer</td><td>用于获取几何类型的空间参考系</td></tr>\n",
    "    <tr><td><b>5</b></td><td><b>Envelope() : Geometry</b></td><td><b>用于获取Geometry的最小边界矩形</b></td></tr>\n",
    "    <tr><td>6</td><td>AsText() : String</td><td>返回WKT (Well-Known Text)的表达形式，不包含SRID元数据</td></tr>\n",
    "    <tr><td>7</td><td>AsBinary() : String</td><td>返回WKB (Well-Known Binary)的表达形式，不包含SRID元数据</td></tr>\n",
    "    <tr><td>8</td><td>IsEmpty() : Boolean</td><td>判断几何类型是否为空</td></tr>\n",
    "    <tr><td>9</td><td>IsSimple() : Boolean</td><td>判断几何类型是否是简单的</td></tr>\n",
    "    <tr><td>10</td><td>Is3D() : Boolean</td><td>判断几何类型是否有z坐标</td></tr>\n",
    "    <tr><td>11</td><td>IsMeasured() : Boolean</td><td>判断几何类型是否有M值</td></tr>\n",
    "    <tr><td><b>12</b></td><td><b>Boundary() : Geometry</b></td><td><b>获取几何类型的边界</b></td></tr>\n",
    "</table>\n",
    "\n",
    "**常规GIS分析方法**\n",
    "<table>\n",
    "    <tr><td>13</td><td>Distance(another: Geometry) : Distance</td><td>求本Geometry与另一个Geometry间的距离</td></tr>\n",
    "    <tr><td>14</td><td>Buffer(distance: Distance) : Geometry</td><td>求本Geometry满足某个距离要求的缓冲区</td></tr>\n",
    "    <tr><td>15</td><td>ConvexHull() : Geometry</td><td>求本Geometry的凸包</td></tr>\n",
    "    <tr><td><b>16</b></td><td><b>Intersection(another : Geometry) : Geometry</b></td><td><b>求本Geometry与另一个Geometry的交</b></td></tr>\n",
    "    <tr><td>17</td><td>Union(another : Geometry) : Geometry</td><td>求本Geometry与另一个Geometry的并</td></tr>\n",
    "    <tr><td>18</td><td>Difference(another : Geometry) : Geometry</td><td>求本Geometry与另一个Geometry的差</td></tr>\n",
    "    <tr><td>19</td><td>SymDifference(another : Geometry) : Geometry</td><td>求本Geometry与另一个Geometry的对称差</td></tr>\n",
    " </table> \n",
    "\n",
    "**空间查询方法**\n",
    "<table>\n",
    "    <tr><td>20</td><td>Equals(another : Geometry) : Boolean</td><td>判断本Geometry与另一个Geometry是否相等</td></tr>\n",
    "    <tr><td>21</td><td>Disjoint(another : Geometry) : Boolean</td><td>判断本Geometry与另一个Geometry是否相离</td></tr>\n",
    "    <tr><td>22</td><td>Intersects(another : Geometry) : Boolean</td><td>判断本Geometry与另一个Geometry是否相交</td></tr>\n",
    "    <tr><td><b>23</b></td><td><b>Touches(another : Geometry) : Boolean</b></td><td><b>判断本Geometry与另一个Geometry是否相接</b></td></tr>\n",
    "    <tr><td>24</td><td>Crosses(another : Geometry) : Boolean</td><td>判断本Geometry是否穿越另一个Geometry</td></tr>\n",
    "    <tr><td>25</td><td>Within(another : Geometry) : Boolean</td><td>判断本Geometry是否包含于另一个Geometry</td></tr>\n",
    "    <tr><td>26</td><td>Contains(another : Geometry) : Boolean</td><td>判断本Geometry是否包含另一个Geometry</td></tr>\n",
    "    <tr><td><b>27</b></td><td><b>Overlaps(another : Geometry) : Boolean</b></td><td><b>判断本Geometry与另一个Geometry是否交叠</b></td></tr>\n",
    "    <tr><td>28</td><td>Relates(another : Geometry, matrix : String) : Boolean</td><td>判断本Geometry与另一个Geometry是否符合给定的9交矩阵 </td></tr>\n",
    "    <tr><td>29</td><td>LocateAlong(mValue : Double) : Geometry</td><td>选取M值为mValue的点，形成一个新的Geometry</td></tr>\n",
    "    <tr><td>30</td><td>LocateBetween(mStart : Double, mEnd : Double) : Geometry</td><td>选取M值在mStart和mEnd之间的点，形成一个新的Geometry</td></tr>\n",
    " </table>   \n",
    "\n",
    "通过pgAdmin 4在PostgreSQL数据库中创建hw7数据库，添加postgis和pgrouting扩展(create extension postgis; create extension pgrouting)，利用数据库<a href=\"http://www.postgresql.org/docs/current/static/backup-dump.html\" target=\"_blank\">备份和恢复</a>功能，恢复hw7数据库(hw7.sql)，并连接该数据库。本次作业假设所有点都在**2D平面**上，即二维笛卡尔坐标系，不考虑球表面（<a href=\"http://postgis.net/docs/using_postgis_dbmanagement.html#PostGIS_Geography\" target=\"_blank\">geography类型</a>），所有的几何都是简单几何，请用PostgreSQL的<a href=\"http://www.postgresql.org/docs/current/static/plpgsql.html\" target=\"_blank\">PL/pgSQL语言</a>实现上述黑体函数的简化版本的函数。\n",
    "\n",
    "PostgreSQL的<a href=\"http://www.postgresql.org/docs/current/static/functions-math.html\" target=\"_blank\">数学函数库</a>给出了常用的数学函数，例如sin、sqrt等。除题目特别说明外，PostGIS中可以使用的函数如下：\n",
    "* ST_GeometryType\n",
    "* ST_NumGeometries\n",
    "* ST_GeometryN\n",
    "* ST_NumPoint\n",
    "* ST_PointN\n",
    "* ST_X\n",
    "* ST_Y\n",
    "* ST_MakePoint\n",
    "* ST_MakeLine\n",
    "\n",
    "作业使用OpenStreetMap上的杭州道路数据，包括road(道路数据)、edge(pgrouting网络分析后获得道路网络)和node(pgrouting网络分析后获得的网络节点)，在pgAdmin 4中查看各关系的数据，熟悉各关系中包含的属性，道路网络已经构建完成，无需修改关系数据。几何展示使用display函数，其参数至少包含gid，name和geom属性。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "from geom_display import display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql postgresql://postgres:postgres@localhost:5432/hw7\n",
    "\n",
    "SET statement_timeout = 0;\n",
    "SET lock_timeout = 0;\n",
    "SET client_encoding = 'utf-8';\n",
    "SET standard_conforming_strings = on;\n",
    "SET check_function_bodies = false;\n",
    "SET client_min_messages = notice;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 辅助函数（10分）\n",
    "\n",
    "实现以下辅助函数，在实现后面的函数时，可直接使用前面已实现的函数。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1 最值判断 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function min(x1 float,  x2 float) \n",
    "    returns float\n",
    "as $$\n",
    "begin\n",
    "    if x1 > x2 then\n",
    "        return x2;\n",
    "    else\n",
    "        return x1;\n",
    "    end if;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function max(x1 float,  x2 float) \n",
    "    returns float\n",
    "as $$\n",
    "begin\n",
    "    if x1 > x2 then\n",
    "        return x1;\n",
    "    else\n",
    "        return x2;\n",
    "    end if;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min</th>\n",
       "        <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.0</td>\n",
       "        <td>3.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2.0, 3.0)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select min(2, 3), max(2, 3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 2D向量运算\n",
    "\n",
    "二维空间内有两个向量$\\vec a = (x_1, y_1)$和$\\vec b = (x_2, y_2)$，定义它们的数量积（又叫内积、点积）为以下实数：\n",
    "\n",
    "$\\vec a \\bullet \\vec b = x_1 x_2 + y_1 y_2$\n",
    "\n",
    "定义它们的向量积（又叫叉积）为向量：\n",
    "\n",
    "$\\vec a \\times \\vec b = x_1 y_2 - x_2 y_1$\n",
    "\n",
    "方向为垂直于二维空间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function dot(x1 float, y1 float, x2 float, y2 float) \n",
    "    returns float\n",
    "as $$\n",
    "begin\n",
    "    return x1 * x2 + y1 * y2;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function cross(x1 float, y1 float, x2 float, y2 float) \n",
    "    returns float\n",
    "as $$\n",
    "begin\n",
    "    return x1 * y2 - x2 * y1;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>dot</th>\n",
       "        <th>dot_1</th>\n",
       "        <th>dot_2</th>\n",
       "        <th>cross</th>\n",
       "        <th>cross_1</th>\n",
       "        <th>cross_2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11.0</td>\n",
       "        <td>-2.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-2.0</td>\n",
       "        <td>0.0</td>\n",
       "        <td>-2.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(11.0, -2.0, 0.0, -2.0, 0.0, -2.0)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select dot(1, 2, 3, 4), dot(-1, 1, 1, -1), dot(1, 1, 1, -1), cross(1, 2, 3, 4), cross(-1, 1, 1, -1), cross(1, 1, 1, -1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 笛卡尔距离计算\n",
    "\n",
    "    float ST_P2PDistance(float x1, float y1, float x2, float y2)\n",
    "    \n",
    "计算点(x1, y1)和点(x2, y2)之间的二维笛卡尔距离"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_P2PDistance(x1 float, y1 float, x2 float, y2 float) \n",
    "    returns float\n",
    "as $$\n",
    "begin\n",
    "    return sqrt((x2 - x1) * (x2 - x1) + (y2 - y1) * (y2 - y1));\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "点(103.5, 200.4) 和 点(200.6, 100.7)的笛卡尔距离"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>st_p2pdistance</th>\n",
       "        <th>st_distance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2.121320343559635</td>\n",
       "        <td>2.121320343559635</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2.121320343559635, 2.121320343559635)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select ST_P2PDistance(103.5, 200.4, 105.6, 200.7), \n",
    "       ST_Distance(ST_GeomFromText('Point(103.5 200.4)'), ST_GeomFromText('Point(105.6 200.7)'));"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 点相等判断\n",
    "\n",
    "    bool ST_PointEqual(geometry g1, geometry g2)\n",
    "    \n",
    "判断点g1和g2是否表示同一地理位置，如果g1或g2不是ST_Point类型，返回NULL  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_PointEquals(g1 geometry, g2 geometry)\n",
    "    returns boolean\n",
    "as $$\n",
    "begin\n",
    "    if ST_GeometryType(g1) != 'ST_Point' or ST_GeometryType(g2) != 'ST_Point' then \n",
    "        return NULL; \n",
    "    end if; \n",
    "    \n",
    "    return (ST_X(g1) = ST_X(g2) and ST_Y(g1) = ST_Y(g2));\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw7\n",
      "1 rows affected.\n",
      "False True None None\n"
     ]
    }
   ],
   "source": [
    "result1 = %sql select ST_PointEquals(ST_GeomFromText('Point(10 10)'), ST_GeomFromText('Point(10 20)'))\n",
    "result2 = %sql select ST_PointEquals(ST_GeomFromText('Point(10 10)'), ST_GeomFromText('Point(10 10)'))\n",
    "result3 = %sql select ST_PointEquals(ST_GeomFromText('Point(10 10)'), ST_GeomFromText('LineString(10 10, 10 20)'))\n",
    "result4 = %sql select ST_PointEquals(ST_GeomFromText('MultiPoint((10 10), (10 20))'), ST_GeomFromText('Point(10 10)'))\n",
    "\n",
    "print(result1[0][0], result2[0][0], result3[0][0], result4[0][0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.5 线段相交判断（5分）\n",
    "\n",
    "    bool ST_LineIntersects(float x1, float y1, float x2, float y2, float x3, float y3, float x4, float y4)\n",
    "\n",
    "判断线段[(x1, y1), (x2, y2)]和线段[(x3, y3), (y4, y4)]在二维笛卡尔空间是否相交\n",
    "\n",
    "算法描述：\n",
    "* 将线段[P1(x1, y1), P2(x2, y2)]表示为显示的直线公式ax + by + c = 0，推导a、b和c的表达式，为避免除零，表达式中不能包含分母\n",
    "* 将线段[P3(x3, y3), P4(y4, y4)]表示为隐式的直线公式P = P3 + (P4 - P3) \\* t，其中t为参数，思考线段[(x3, y3), (y4, y4)]上对应的参数t的范围。将矢量表达式转化为x和y的表达式，x = x3 + (x4 - x3) * t，推导y的表达式\n",
    "* 将x和y的表达式，带入ax + by + c = 0，化简为dt = e，推导d和e的表达式，t为待求解的未知数\n",
    "* 求解dt = e：\n",
    "    * 如果d为0(abs(d) < 1e-6)：\n",
    "        * 如果e不为0(abs(e) >= 1e-6)，则表示这两条直线是什么关系？\n",
    "        * 如果e为0，则表示这两条直线共线，计算(P2 - P1)归一化向量和(P3 - P1)向量的内积为t1，(P2 - P1)归一化向量和(P4 - P1)向量的内积为t2，t1和t2表示P1到P3和P4的距离，t1和t2满足什么条件时，这两个线段有重叠线段\n",
    "    * 如果d不为0：\n",
    "        * t = e / d，t满足什么条件时，表示交点在线段[P3, P4]上\n",
    "        * 通过P = P3 + (P4 - P3) \\* t计算交点，判断交点P是否在[P1, P2]上（前面给出了类似思路）\n",
    "        \n",
    "基于上述算法描述，完成线段相交判断代码，从begin到end之间的代码行数小于30行（大于30行的话，你可能把问题想复杂了）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_LineIntersects(x1 float, y1 float, x2 float, y2 float, x3 float, y3 float, x4 float, y4 float) \n",
    "    returns boolean\n",
    "as $$\n",
    "declare d float;\n",
    "        e float;\n",
    "        t float;\n",
    "        t1 float;\n",
    "        t2 float;\n",
    "        x float;\n",
    "        y float;\n",
    "begin\n",
    "    d = (y2-y1)*(x4-x3)+(x1-x2)*(y4-y3);\n",
    "    e = (y1-y2)*x3+(x2-x1)*y3+x1*y2-x2*y1;\n",
    "    if abs(d)<1e-6 then\n",
    "        if abs(e) >=1e-6 then\n",
    "            return False;\n",
    "        else\n",
    "            t1 = dot((x2-x1),(y2-y1),(x3-x1),(y3-y1));\n",
    "            t2 = dot((x2-x1),(y2-y1),(x4-x1),(y4-y1));\n",
    "            if (t1<0 and t2<0) or ( t1>dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) and t2 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) ) then \n",
    "                return False;\n",
    "            else return True;\n",
    "            end if;\n",
    "        end if;\n",
    "    else\n",
    "        t = e / d;\n",
    "        if t >= 0 and t <= 1 then\n",
    "            x = x3+(x4-x3)*t;\n",
    "            y = y3+(y4-y3)*t;\n",
    "            if abs( ST_P2PDistance(x1,y1,x2,y2) - (ST_P2PDistance(x,y,x2,y2)+ST_P2PDistance(x1,y1,x,y))) > 1e-6 then\n",
    "                return False;\n",
    "            else return True;\n",
    "            end if;\n",
    "        else return False;\n",
    "        end if;\n",
    "    end if;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_LineIntersects函数封装"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_WLineIntersects(g1 geometry, g2 geometry)\n",
    "    returns boolean\n",
    "as $$\n",
    "declare p1 geometry;\n",
    "        p2 geometry;\n",
    "        p3 geometry;\n",
    "        p4 geometry;\n",
    "begin\n",
    "    if ST_GeometryType(g1) != 'ST_LineString' or ST_NumPoints(g1) != 2 or\n",
    "       ST_GeometryType(g2) != 'ST_LineString' or ST_NumPoints(g2) != 2 then \n",
    "        return NULL; \n",
    "    end if;\n",
    "\n",
    "    p1 = ST_PointN(g1, 1);\n",
    "    p2 = ST_PointN(g1, 2);\n",
    "    p3 = ST_PointN(g2, 1);\n",
    "    p4 = ST_PointN(g2, 2);\n",
    "    \n",
    "    return ST_LineIntersects(ST_X(p1), ST_Y(p1), ST_X(p2), ST_Y(p2), ST_X(p3), ST_Y(p3), ST_X(p4), ST_Y(p4));\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_LineIntersects函数测试（包含了作业要求的所有测试）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "34 / 34 tests are passed\n"
     ]
    }
   ],
   "source": [
    "linegroupA = [\"LineString(10 10, 10 20)\", \"LineString(-10 -10, -20 -10)\", \"LineString(0 0, 10 10)\",   \"LineString(-1 -1, -11 11)\",\n",
    "              \"LineString(10 10, 20 20)\", \"LineString(20 20, 10 10)\",     \"LineString(10 10, 20 20)\", \"LineString(20 20, 10 10)\",\n",
    "              \"LineString(10 10, 20 20)\", \"LineString(20 20, 10 10)\",     \"LineString(0 0, 10 10)\",   \"LineString(0 0, 10 10)\",\n",
    "              \"LineString(0 0, 10 10)\",   \"LineString(0 0, 10 10)\",       \"LineString(0 0, 10 10)\",   \"LineString(0 0, 10 10)\",\n",
    "              \"LineString(0 0, 10 10)\"\n",
    "             ]\n",
    "linegroupB = [\"LineString(20 20, 20 30)\", \"LineString(-20 -20, -30 -20)\", \"LineString(0 1, 10 11)\",   \"LineString(-2 -3, -12 9 )\",\n",
    "              \"LineString(30 30, 40 40)\", \"LineString(10 10, 0   0)\",     \"LineString(12 12, 18 18)\", \"LineString(0 0, 15 15)\",\n",
    "              \"LineString(18 18, 20 20)\", \"LineString(20 20, 10 10)\",     \"LineString(10 0, 0 10)\",   \"LineString(30 0, 0 20)\",\n",
    "              \"LineString(20 20, 10 10)\", \"LineString(-5 0, 0 -5)\",       \"LineString(5 0, 5 -5)\",    \"LineString(0 5, 5 5)\",\n",
    "              \"LineString(0 0, 0 10)\"\n",
    "             ]\n",
    "template = \"SELECT ST_WLineIntersects('%s'::geometry, '%s'::geometry), ST_Intersects('%s'::geometry, '%s'::geometry)\"\n",
    "\n",
    "passedTests = len(linegroupA) * 2\n",
    "for i in range(len(linegroupA)):\n",
    "    lineA  = linegroupA[i]\n",
    "    lineB  = linegroupB[i]\n",
    "    query  = template % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersect test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "    \n",
    "    lineA  = linegroupB[i]\n",
    "    lineB  = linegroupA[i]\n",
    "    query  = template % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersect test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "            \n",
    "print(str(passedTests) + ' / ' + str(len(linegroupA) * 2) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.6 线段与线段交集（5分）\n",
    "\n",
    "    geometry ST_LineIntersection(float x1, float y1, float x2, float y2, float x3, float y3, float x4, float y4)\n",
    "\n",
    "判断线段[(x1, y1), (x2, y2)]和线段[(x3, y3), (y4, y4)]在二维笛卡尔空间的交集，算法思想与上题类似。忽略LineString(10 10, 20 20)和LineString(20 20, 10 10)的差异，即与ST_Intersection结果只要ST_Equals就满足题目要求。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_LineIntersection(x1 float, y1 float, x2 float, y2 float, x3 float, y3 float, x4 float, y4 float) \n",
    "    returns geometry\n",
    "as $$\n",
    "declare d float;\n",
    "        e float;\n",
    "        t float;\n",
    "        t1 float;\n",
    "        t2 float;\n",
    "        x float;\n",
    "        y float;\n",
    "begin\n",
    "    if x1 = x2 and x3 = x4 and y1 = y2 and y3 = y4 then\n",
    "        return 'LINESTRING EMPTY'::geometry;\n",
    "    end if;\n",
    "    d = (y2-y1)*(x4-x3)+(x1-x2)*(y4-y3);\n",
    "    e = (y1-y2)*x3+(x2-x1)*y3+x1*y2-x2*y1;\n",
    "    if abs(d)<1e-6 then\n",
    "        if abs(e) >=1e-6 then\n",
    "            return 'LINESTRING EMPTY'::geometry;\n",
    "        else\n",
    "            t1 = dot((x2-x1),(y2-y1),(x3-x1),(y3-y1));\n",
    "            t2 = dot((x2-x1),(y2-y1),(x4-x1),(y4-y1));\n",
    "            if (t1<0 and t2<0) or ( t1>dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) and t2 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) ) then \n",
    "                return 'LINESTRING EMPTY'::geometry;\n",
    "            else \n",
    "                if (t1 = 0 and t2 <=0) or (t1 = dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) and t2 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) ) then\n",
    "                    return ST_MakePoint(x3,y3);\n",
    "                end if;\n",
    "                if (t2 = 0 and t1 <=0) or (t2 = dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) and t1 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) ) then\n",
    "                    return ST_MakePoint(x4,y4);\n",
    "                end if;\n",
    "                if t1 < 0 and t2 > 0 then\n",
    "                    if t2 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) then \n",
    "                        return ST_MakeLine(ST_MakePoint(x1,y1),ST_MakePoint(x2,y2));\n",
    "                    else\n",
    "                        return ST_MakeLine(ST_MakePoint(x1,y1),ST_MakePoint(x4,y4));\n",
    "                    end if;\n",
    "                end if;\n",
    "                if t2 < 0 and t1 > 0 then\n",
    "                    if t1 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) then \n",
    "                        if ST_MakePoint(x2,y2) = ST_MakePoint(x1,y1) then\n",
    "                            return ST_MakePoint(x2,y2);\n",
    "                        end if;\n",
    "                        return ST_MakeLine(ST_MakePoint(x1,y1),ST_MakePoint(x2,y2));\n",
    "                    else\n",
    "                        if ST_MakePoint(x3,y3) = ST_MakePoint(x1,y1) then\n",
    "                            return ST_MakePoint(x3,y3);\n",
    "                        end if;\n",
    "                        return ST_MakeLine(ST_MakePoint(x1,y1),ST_MakePoint(x3,y3));\n",
    "                    end if;\n",
    "                end if;\n",
    "                if t1 >= 0 and t2 >= 0 then\n",
    "                    if ( t1 <= dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) and t2 <= dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) ) then\n",
    "                        if ST_MakePoint(x3,y3) = ST_MakePoint(x4,y4) then\n",
    "                            return ST_MakePoint(x4,y4);\n",
    "                        end if;\n",
    "                        return ST_MakeLine(ST_MakePoint(x3,y3),ST_MakePoint(x4,y4));\n",
    "                    end if;\n",
    "                    if t2 > dot((x2-x1),(y2-y1),(x2-x1),(y2-y1)) then \n",
    "                        if ST_MakePoint(x3,y3) = ST_MakePoint(x2,y2) then\n",
    "                            return ST_MakePoint(x3,y3);\n",
    "                        end if;\n",
    "                        return ST_MakeLine(ST_MakePoint(x3,y3),ST_MakePoint(x2,y2));\n",
    "                    else\n",
    "                        if ST_MakePoint(x4,y4) = ST_MakePoint(x2,y2) then\n",
    "                            return ST_MakePoint(x4,y4);\n",
    "                        end if;\n",
    "                        return ST_MakeLine(ST_point(x4,y4),ST_point(x2,y2));\n",
    "                    end if;\n",
    "                end if;\n",
    "                return ST_MakeLine(ST_point(x3,y3),ST_point(x4,y4));\n",
    "            end if;\n",
    "        end if;\n",
    "    else\n",
    "        t = e / d;\n",
    "        if t >= 0 and t <= 1 then\n",
    "            x = x3+(x4-x3)*t;\n",
    "            y = y3+(y4-y3)*t;\n",
    "            if abs( ST_P2PDistance(x1,y1,x2,y2) - (ST_P2PDistance(x,y,x2,y2)+ST_P2PDistance(x1,y1,x,y))) > 1e-6 then\n",
    "                return 'LINESTRING EMPTY'::geometry;\n",
    "            else return ST_MakePoint(x,y);\n",
    "            end if;\n",
    "        else return 'LINESTRING EMPTY'::geometry;\n",
    "        end if;\n",
    "    end if;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_LineIntersection函数封装"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_WLineIntersection(g1 geometry, g2 geometry)\n",
    "    returns geometry\n",
    "as $$\n",
    "declare p1 geometry;\n",
    "        p2 geometry;\n",
    "        p3 geometry;\n",
    "        p4 geometry;\n",
    "begin\n",
    "    if ST_GeometryType(g1) != 'ST_LineString' or ST_NumPoints(g1) != 2 or\n",
    "       ST_GeometryType(g2) != 'ST_LineString' or ST_NumPoints(g2) != 2 then \n",
    "        return NULL; \n",
    "    end if;\n",
    "\n",
    "    p1 = ST_PointN(g1, 1);\n",
    "    p2 = ST_PointN(g1, 2);\n",
    "    p3 = ST_PointN(g2, 1);\n",
    "    p4 = ST_PointN(g2, 2);\n",
    "    \n",
    "    return ST_LineIntersection(ST_X(p1), ST_Y(p1), ST_X(p2), ST_Y(p2), ST_X(p3), ST_Y(p3), ST_X(p4), ST_Y(p4));\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_WLineIntersection(g1 geometry, g2 geometry, g3 geometry, g4 geometry)\n",
    "    returns geometry\n",
    "as $$\n",
    "begin\n",
    "    if ST_GeometryType(g1) != 'ST_Point' or ST_GeometryType(g2) != 'ST_Point' or\n",
    "       ST_GeometryType(g4) != 'ST_Point' or ST_GeometryType(g4) != 'ST_Point' then \n",
    "        return NULL; \n",
    "    end if;\n",
    "    \n",
    "    return ST_LineIntersection(ST_X(g1), ST_Y(g1), ST_X(g2), ST_Y(g2), ST_X(g3), ST_Y(g3), ST_X(g4), ST_Y(g4));\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_LineIntersection函数测试，后面函数需要用到ST_LineIntersection，确保通过所有测试（包含了作业要求的所有测试）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "34 / 34 tests are passed\n"
     ]
    }
   ],
   "source": [
    "linegroupA = [\"LineString(10 10, 10 20)\", \"LineString(-10 -10, -20 -10)\", \"LineString(0 0, 10 10)\",   \"LineString(-1 -1, -11 11)\",\n",
    "              \"LineString(10 10, 20 20)\", \"LineString(20 20, 10 10)\",     \"LineString(10 10, 20 20)\", \"LineString(20 20, 10 10)\",\n",
    "              \"LineString(10 10, 20 20)\", \"LineString(20 20, 10 10)\",     \"LineString(0 0, 10 10)\",   \"LineString(0 0, 10 10)\",\n",
    "              \"LineString(0 0, 10 10)\",   \"LineString(0 0, 10 10)\",       \"LineString(0 0, 10 10)\",   \"LineString(0 0, 10 10)\",\n",
    "              \"LineString(0 0, 10 10)\"\n",
    "             ]\n",
    "linegroupB = [\"LineString(20 20, 20 30)\", \"LineString(-20 -20, -30 -20)\", \"LineString(0 1, 10 11)\",   \"LineString(-2 -3, -12 9 )\",\n",
    "              \"LineString(30 30, 40 40)\", \"LineString(10 10, 0   0)\",     \"LineString(12 12, 18 18)\", \"LineString(0 0, 15 15)\",\n",
    "              \"LineString(18 18, 20 20)\", \"LineString(20 20, 10 10)\",     \"LineString(10 0, 0 10)\",   \"LineString(30 0, 0 20)\",\n",
    "              \"LineString(20 20, 10 10)\", \"LineString(-5 0, 0 -5)\",       \"LineString(5 0, 5 -5)\",    \"LineString(0 5, 5 5)\",\n",
    "              \"LineString(0 0, 0 10)\"\n",
    "             ]\n",
    "template = \"\"\"SELECT ST_AsText(ST_WLineIntersection('%s'::geometry, '%s'::geometry)), \n",
    "                     ST_AsText(ST_Intersection('%s'::geometry, '%s'::geometry))\"\"\"\n",
    "\n",
    "passedTests = len(linegroupA) * 2\n",
    "for i in range(len(linegroupA)):\n",
    "    lineA  = linegroupA[i]\n",
    "    lineB  = linegroupB[i]\n",
    "    query  = template % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersection test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "    \n",
    "    lineA  = linegroupB[i]\n",
    "    lineB  = linegroupA[i]\n",
    "    query  = template % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersection test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "            \n",
    "print(str(passedTests) + ' / ' + str(len(linegroupA) * 2) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 常规方法（11分）\n",
    "\n",
    "### 2.1  轴对齐的包围盒（4分）\n",
    "\n",
    "轴对齐的包围盒(Axis-Aligned Bounding Box) <a href=\"http://postgis.net/docs/ST_Envelope.html\" target=\"_blank\">ST_Envelope</a>\n",
    "\n",
    "    geometry ST_AABBEnvelope(geometry g)\n",
    "\n",
    "可以使用ST_DumpPoints和ST_MakeEnvelope函数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_AABBEnvelope(g geometry)\n",
    "    returns geometry\n",
    "as $$\n",
    "declare result geometry;\n",
    "begin\n",
    "    if ST_GeometryType(g) = 'ST_Point' then\n",
    "        return g;\n",
    "    end if;\n",
    "    select ST_MakeEnvelope(min(st_x(geom)),min(st_y(geom)), max(st_X(geom)),max(st_y(geom))) into result from ST_DumpPoints(g) g1;\n",
    "    return result;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_AABBEnvelope函数测试（包含了作业要求的所有测试）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "7 / 7 tests are passed\n"
     ]
    }
   ],
   "source": [
    "geometries = [\"Point(10 10)\", \n",
    "              \"MultiPoint((10 10), (20 20))\",\n",
    "              \"LineString(10 10, 20 20)\", \n",
    "              \"LineString(10 10, 20 20, 30 20)\", \n",
    "              \"MultiLineString((10 10, 20 20, 30 20), (30 40, 40 50))\", \n",
    "              \"Polygon((0 0, 0 1, 0.5 2, 1 1, 1 0, 0 0))\",\n",
    "              \"MultiPolygon(((1 1, 5 0, 6 7, 1 1)), ((-1 -1, -2 5, 0 0, -1 -1)))\"\n",
    "             ]\n",
    "template = \"SELECT ST_AsText(ST_AABBEnvelope('%s'::geometry)), ST_AsText(ST_Envelope('%s'::geometry))\"\n",
    "\n",
    "passedTests = len(geometries)\n",
    "for geometry in geometries:\n",
    "    query  = template % (geometry, geometry)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(geometry + ' envelope test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "\n",
    "print(str(passedTests) + ' / ' + str(len(geometries)) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 几何边界（7分）\n",
    "几何边界函数 <a href=\"http://postgis.net/docs/ST_Boundary.html\" target=\"_blank\">ST_Boundary</a>\n",
    "\n",
    "\tgeometry ST_GeomBoundary(geometry g)\n",
    "    \n",
    "可以使用ST_StartPoint、ST_EndPoint、ST_ExteriorRing、ST_NumInteriorRings、ST_InteriorRingN、ST_Union、ST_Intersection、ST_Difference和ST_Collect函数，需要使用数组。要求实现Point、LineString、Polygon、MultiPoint、MultiLineString和MultiPolygon几何类型的边界获取，其他类型返回GEOMETRYCOLLECTION EMPTY。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_GeomBoundary(geom geometry)\n",
    "    returns geometry\n",
    "as $$\n",
    "declare i int;\n",
    "        gc geometry[] = '{}';\n",
    "        g geometry;\n",
    "        gx geometry;\n",
    "        agx geometry;\n",
    "begin\n",
    "    if ST_GeometryType(geom) = 'ST_Point' or ST_GeometryType(geom) = 'ST_MultiPoint' then\n",
    "        return 'GEOMETRYCOLLECTION EMPTY'::geometry;\n",
    "    end if;\n",
    "     if ST_GeometryType(geom) = 'ST_LineString' then\n",
    "        if ST_StartPoint(geom) = ST_EndPoint(geom) then\n",
    "            return 'MULTIPOINT EMPTY'::geometry;\n",
    "        else\n",
    "            return ST_Collect(ST_StartPoint(geom),ST_EndPoint(geom));\n",
    "        end if;\n",
    "    end if;\n",
    "    if ST_GeometryType(geom) = 'ST_MultiLineString' then\n",
    "        i = 1;\n",
    "        g = 'MULTIPOINT EMPTY'::geometry;\n",
    "        loop\n",
    "        exit when i > ST_NumGeometries(geom);\n",
    "            gx = ST_GeometryN(geom,i);\n",
    "            i = i+1;\n",
    "            if i <= ST_NumGeometries(geom) then\n",
    "                agx = ST_GeometryN(geom,i);\n",
    "            else\n",
    "                agx = 'MULTIPOINT EMPTY'::geometry;\n",
    "            end if;\n",
    "            if ( ST_StartPoint(gx) = ST_StartPoint(agx) or ST_EndPoint(gx) = ST_EndPoint(agx) ) or ST_StartPoint(gx) = ST_EndPoint(gx) then\n",
    "                g = g;\n",
    "                i = i+1;\n",
    "            else\n",
    "                if g = 'MULTIPOINT EMPTY'::geometry then\n",
    "                    g = ST_StartPoint(gx);\n",
    "                else\n",
    "                    g = sT_union(ST_StartPoint(gx),g);\n",
    "                end if;\n",
    "                g = sT_union(ST_EndPoint(gx),g);\n",
    "            end if;\n",
    "        end loop;\n",
    "        return g;\n",
    "    end if;\n",
    "    if ST_GeometryType(geom) = 'ST_Polygon' then\n",
    "        i = 1;\n",
    "        gc = array_append(gc,ST_ExteriorRing(geom));\n",
    "        loop\n",
    "            exit when i > ST_NumInteriorRings(geom);\n",
    "            gx = ST_InteriorRingN(geom,i);\n",
    "            gc = array_append(gc,gx);\n",
    "            i = i+1;\n",
    "        end loop;\n",
    "        return ST_Collect(gc);\n",
    "    end if;\n",
    "    if ST_GeometryType(geom) = 'ST_MultiPolygon' then\n",
    "        for i in 1..ST_NumGeometries(geom) loop\n",
    "             g = ST_Collect(g,ST_GeomBoundary(ST_GeometryN(geom, i)));\n",
    "        end loop;\n",
    "        return g;\n",
    "    end if;\n",
    "    return ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_GeomBoundary函数测试（包含了作业要求的所有测试）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "8 / 8 tests are passed\n"
     ]
    }
   ],
   "source": [
    "geometries = [\"Point(10 50)\", \n",
    "              \"MultiPoint(10 50, 40 60)\",\n",
    "              \"LineString(10 50, 20 30)\",\n",
    "              \"LineString(10 50, 20 30, 10 50)\", \n",
    "              \"MultiLineString((10 50, 20 30, 10 30),(30 60, 50 60, 10 40))\",\n",
    "              \"MultiLineString((10 50, 20 30, 10 30),(10 50, 50 60, 10 30))\",\n",
    "              \"Polygon((10 50, 20 30, 40 60, 10 50),(10 50, 20 30, 40 60, 10 50),(10 50, 20 30, 40 60, 10 50))\",\n",
    "              \"MultiPolygon(((10 50, 20 30, 40 60, 10 50),(10 50, 20 30, 40 60, 10 50)))\"\n",
    "             ]\n",
    "template = \"SELECT ST_AsText(ST_GeomBoundary('%s'::geometry)), ST_AsText(ST_Boundary('%s'::geometry))\"\n",
    "\n",
    "passedTests = len(geometries)\n",
    "for geometry in geometries:\n",
    "    query  = template % (geometry, geometry)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(geometry + ' boundary test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "\n",
    "print(str(passedTests) + ' / ' + str(len(geometries)) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 常规GIS分析方法（5分）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1 线段与线段交集（5分）\n",
    "\n",
    "几何交集函数 <a href=\"http://postgis.net/docs/ST_Intersection.html\" target=\"_blank\">ST_Intersection</a>\n",
    "\n",
    "    geometry ST_LineIntersection(geometry g1, geometry g2)\n",
    "\n",
    "\n",
    "几何要素g1和g2类型为ST_LineString，计算折线与折线的交集，当几何类型不符合要求时，返回GEOMETRYCOLLECTION EMPTY。可以使用ST_Union函数和1.6线段与线段交集函数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_LineIntersection(g1 geometry, g2 geometry) \n",
    "    returns geometry\n",
    "as $$\n",
    "declare g geometry[] = '{}';\n",
    "        i int;\n",
    "        j int;\n",
    "begin\n",
    "    if ST_GeometryType(g1) != 'ST_LineString' or ST_GeometryType(g2) != 'ST_LineString' then\n",
    "        return ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');\n",
    "    end if;\n",
    "    for i in 1..(ST_NumPoints(g1)-1) loop\n",
    "        for j in 1..(ST_NumPoints(g2)-1) loop\n",
    "            g = array_append(g, ST_WLineIntersection(ST_PointN(g1,i),ST_PointN(g1,i+1),ST_PointN(g2,j),ST_PointN(g2,j+1)));\n",
    "        end loop; \n",
    "    end loop;\n",
    "    return ST_Union(g);\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_LineIntersection函数测试，仅包含了作业要求的**部分测试**，请自己增加测试用例，成绩将通过其他测试集进行评测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "LineString(4 4,0 0, 3 3) and LineString(5 4, 1 1, 2 2) intersection test failed\n",
      "Your result is LINESTRING(1 1,2 2)\n",
      "PGIS result is LINESTRING(2 2,1 1)\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "10 / 11 tests are passed\n"
     ]
    }
   ],
   "source": [
    "linegroupA = [\"LineString(3 3, 2 2)\",\"LineString(1 1, 1 1)\",\"LineString(0 0, 0 0)\",\"LineString(1 0, 0 1, 1 1)\",\"LineString(0 0, 0 0, 1 1)\",\"LineString(4 4,0 0, 3 3)\",\"LineString(4 4,0 0, 3 3)\", \"LineString(-1 1, 1 1)\",\"LineString(0 0, 1 1)\", \"LineString(0.8 0, 0.8 -2)\",\"LineString(0 0, 2 0, 1 2, 0 0)\"]\n",
    "linegroupB = [\"LineString(4 4, 2 2)\",\"LineString(2 2, 2 2)\",\"LineString(0 0, 1 1)\",\"LineString(1 0, 1 0, 1 1)\",\"LineString(0 0, 0 0, 0 0)\",\"LineString(4 4,0 0, 3 3)\",\"LineString(5 4, 1 1, 2 2)\", \"LineString(-2 1, -1 1)\",\"LineString(10 0, -1 10)\",\"LineString(0.8 1.1, 0.8 -0.1)\",\"LineString(1 0, 2 2, 0 2, 1 0)\"]\n",
    "\n",
    "template = \"SELECT ST_Astext(ST_LineIntersection('%s'::geometry, '%s'::geometry)), ST_AsText(ST_Intersection('%s'::geometry, '%s'::geometry))\"\n",
    "\n",
    "passedTests = len(linegroupA)\n",
    "for i in range(len(linegroupA)):\n",
    "    lineA  = linegroupA[i]\n",
    "    lineB  = linegroupB[i]\n",
    "    query  = template % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersection test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "\n",
    "print(str(passedTests) + ' / ' + str(len(linegroupA)) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------- random tests -----------------------------------------\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "10 / 10 tests are passed\n"
     ]
    }
   ],
   "source": [
    "print(\"-------------------------------- random tests -----------------------------------------\")\n",
    "import random\n",
    "import math\n",
    "totaltests = 10\n",
    "passedTests = totaltests\n",
    "template1 = \"SELECT ST_Astext(ST_LineIntersection('LineString(%s)'::geometry, 'LineString(%s)'::geometry)), ST_AsText(ST_Intersection('LineString(%s)'::geometry, 'LineString(%s)'::geometry))\"\n",
    "for i in range(totaltests):\n",
    "    lineA = \"\"\n",
    "    for j in range(int(random.random()*3)+1):\n",
    "        lineA+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))+\", \"\n",
    "    lineA+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))\n",
    "    lineB = \"\"\n",
    "    for j in range(int(random.random()*3)+1):\n",
    "        lineB+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))+\", \"\n",
    "    lineB+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))\n",
    "    query  = template1 % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersection test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "\n",
    "print(str(passedTests) + ' / ' + str(totaltests) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. 空间查询方法（5分+6分）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1 （附加题）折线与折线相接空间关系判断（5分）\n",
    "\n",
    "几何相接空间关系判断函数 <a href=\"http://postgis.net/docs/ST_Touches.html\" target=\"_blank\">ST_Touches</a>\n",
    "\n",
    "\tboolean ST_LineTouches(geometry g1, geometry g2)\n",
    "    \n",
    "几何要素g1和g2类型为ST_LineString，判断线与线是否相接，当几何类型不符合要求时，返回GEOMETRYCOLLECTION EMPTY。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_LineTouches(g1 geometry, g2 geometry)\n",
    "        returns boolean\n",
    "as $$\n",
    "declare g geometry;\n",
    "        i int;\n",
    "        j int;\n",
    "        result boolean;\n",
    "begin\n",
    "    if ST_GeometryType(g1) != 'ST_LineString' or ST_GeometryType(g2) != 'ST_LineString' then\n",
    "        return ST_GeomFromText('GEOMETRYCOLLECTION EMPTY');\n",
    "    end if;\n",
    "    result = False;\n",
    "    for i in 1..(ST_NumPoints(g1)-1) loop\n",
    "        for j in 1..(ST_NumPoints(g2)-1) loop\n",
    "            g = ST_WLineIntersection(ST_PointN(g1,i),ST_PointN(g1,i+1),ST_PointN(g2,j),ST_PointN(g2,j+1));\n",
    "            if ST_GeometryType(g) = 'ST_LineString' then \n",
    "                return False;\n",
    "            end if;\n",
    "            if ST_GeometryType(g) = 'ST_Point' then \n",
    "                if g = ST_PointN(g1,1) or g = ST_PointN(g1,ST_NumPoints(g1)) or g = ST_PointN(g2,1) or g = ST_PointN(g2,ST_NumPoints(g2)) then\n",
    "                    return True;\n",
    "                else \n",
    "                    return False;\n",
    "                end if;\n",
    "            end if;\n",
    "        end loop; \n",
    "    end loop;\n",
    "    return result;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_LineTouches函数测试，仅包含了作业要求的**部分测试**，请自己增加测试用例，成绩将通过其他测试集进行评测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "7 / 7 tests are passed\n"
     ]
    }
   ],
   "source": [
    "linegroupA = [\"LineString(0 1, 0 1)\",\"LineString(1 1, 0 0)\",\"LineString(1 1, 0 0)\",\"LineString(1 1, 0 0)\",\"LineString(1 1, 0 0 , -1 1)\",\"LineString(-1 0, 1 0)\", \"LineString(-1 0, 1 0)\"]\n",
    "linegroupB = [\"LineString(0 1, 0 1)\",\"LineString(1 1, 0 0)\",\"LineString(1 0, 0 0)\",\"LineString(1 0, 1 0)\",\"LineString(-1 0, 1 0)\",\"LineString(-1 1, 0 0 , 1 1)\", \"LineString(-2 0,-1 0)\"]\n",
    "\n",
    "template = \"SELECT ST_LineTouches('%s'::geometry, '%s'::geometry), ST_Touches('%s'::geometry, '%s'::geometry)\"\n",
    "\n",
    "passedTests = len(linegroupA)\n",
    "for i in range(len(linegroupA)):\n",
    "    lineA  = linegroupA[i]\n",
    "    lineB  = linegroupB[i]\n",
    "    query  = template % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' touch test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "\n",
    "print(str(passedTests) + ' / ' + str(len(linegroupA)) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-------------------------------- random tests -----------------------------------------\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1000 / 1000 tests are passed\n"
     ]
    }
   ],
   "source": [
    "print(\"-------------------------------- random tests -----------------------------------------\")\n",
    "import random\n",
    "import math\n",
    "totaltests = 1000\n",
    "passedTests = totaltests\n",
    "template1 = \"SELECT ST_LineTouches('LineString(%s)'::geometry, 'LineString(%s)'::geometry), ST_Touches('LineString(%s)'::geometry, 'LineString(%s)'::geometry)\"\n",
    "for i in range(totaltests):\n",
    "    lineA = \"\"\n",
    "    for j in range(int(random.random()*3)+1):\n",
    "        lineA+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))+\", \"\n",
    "    lineA+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))\n",
    "    lineB = \"\"\n",
    "    for j in range(int(random.random()*3)+1):\n",
    "        lineB+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))+\", \"\n",
    "    lineB+=str(round(random.random(),5))+\" \"+str(round(random.random(),5))\n",
    "    query  = template1 % (lineA, lineB, lineA, lineB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(lineA + ' and ' + lineB + ' intersection test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "\n",
    "print(str(passedTests) + ' / ' + str(totaltests) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 交叠空间关系判断（6分）\n",
    "\n",
    "几何交叠空间关系判断函数 <a href=\"http://postgis.net/docs/ST_Overlaps.html\" target=\"_blank\">ST_Overlaps</a>\n",
    "\n",
    "    boolean ST_GeomOverlaps(geometry g1, geometry g2)\n",
    " \n",
    "根据定义判断几何是否交叠：如Dim(I(a)) = Dim(I(b)) = Dim(I(a)∩I(b))，且a∩b≠a，同时a∩b≠b，则a和b交叠\n",
    "\n",
    "可以使用ST_Dimension、ST_Intersection、ST_Difference和ST_Equals函数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 368,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 368,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_GeomOverlaps(g1 geometry, g2 geometry)\n",
    "    returns boolean\n",
    "as $$\n",
    "begin\n",
    "if ST_Dimension(g1) = ST_Dimension(g2) and ST_Dimension(g2) = ST_Dimension(ST_Intersection(g1,g2)) \n",
    "    and not ST_Equals(g1,ST_Intersection(g1,g2)) and not ST_Equals(g2,ST_Intersection(g1,g2)) \n",
    "    and not ST_Equals(ST_Difference(g1,ST_Intersection(g1,g2)),g1) then\n",
    "    return True;\n",
    "else\n",
    "    return False;\n",
    "end if;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ST_GeomOverlaps函数测试，仅包含了作业要求的**部分测试**，请自己增加测试用例，成绩将通过其他测试集进行评测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 377,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "8 / 8 tests are passed\n"
     ]
    }
   ],
   "source": [
    "geomgroupA = [\"Polygon((0 0, 0 1, 0.5 2, 1 1, 1 0, 0 0))\",\"Point(11 12)\",\"Point(11 12)\",\"Point(11 11)\",\"LineString(0 0, 0 0)\",\"LineString(0 0, 2 2)\", \"LineString(0 0, 2 2)\", \"LineString(0 0, -1 -1)\"]\n",
    "geomgroupB = [\"Polygon((0 0, 0 1, 0.5 1, 1 1, 1 0, 0 0))\",\"LineString(0 0, 0 0)\",\"Point(11 11)\",\"Point(11 11)\",\"LineString(0 0, 0 0)\",\"LineString(2 0, 0 2)\", \"LineString(1 1, 3 3)\", \"LineString(0 0, 1 1)\"]\n",
    "\n",
    "template = \"SELECT ST_GeomOverlaps('%s'::geometry, '%s'::geometry), ST_Overlaps('%s'::geometry, '%s'::geometry)\"\n",
    "\n",
    "passedTests = len(geomgroupA)\n",
    "for i in range(len(geomgroupA)):\n",
    "    geomA  = geomgroupA[i]\n",
    "    geomB  = geomgroupB[i]\n",
    "    query  = template % (geomA, geomB, geomA, geomB)\n",
    "    result = %sql $query\n",
    "    if result[0][0] != result[0][1]:\n",
    "        passedTests -= 1\n",
    "        print(geomA + ' and ' + geomB + ' overlap test failed\\nYour result is ' + str(result[0][0]) + '\\nPGIS result is ' + str(result[0][1]))\n",
    "        \n",
    "print(str(passedTests) + ' / ' + str(len(geomgroupA)) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. 视图与触发器（8分）\n",
    "\n",
    "track关系可以用于分析道路拥堵，即道路上车辆数目。所涉及的数据包括道路数据road(id, name, source, target, geom, len)和车辆位置数据track(time, position, userName, carID)。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.1 首先创建视图CurrentTrack(carID, position, roadID)，表示每辆车**当前**所在位置及该位置所在的道路，即距离所在位置最近的道路。（2分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop view if exists currenttrack;\n",
    "create view CurrentTrack\n",
    "    as \n",
    "select rd.carID, rd.position, rd.id roadID from\n",
    "(select carID, position, min(st_distance(position,geom)) from \n",
    " (select t.carid, position from\n",
    "(select carID ,max(time) from track group by carID) t, track\n",
    "where t.carID = track.carID and t.max = track.time) nowt,\n",
    "road group by carID,position) mind,\n",
    "\n",
    "(select carID, position, st_distance(position,geom) d,id from \n",
    " (select t.carid, position from\n",
    "(select carID ,max(time) from track group by carID) t, track\n",
    "where t.carID = track.carID and t.max = track.time) nowt,\n",
    "road) rd\n",
    "\n",
    "where rd.carID = mind.carID and rd.position = mind.position and rd.d = mind.min group by rd.carID,rd.position,rd.id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.2 基于CurrentTrack视图，构造SQL语句查询道路上车数目，查询返回道路编号和道路上车的数目，按车的数目降序排列。（2分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>roadid</th>\n",
       "        <th>num</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>13306</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2656</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2658</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>14122</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(13306, 4), (2656, 3), (2658, 1), (14122, 1)]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select roadID, count(*) num from CurrentTrack group by roadID order by num desc;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.3 根据**SQL**的视图可更新标准，该视图是否为可更新视图，请说明理由。（1分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "不是。\n",
    "SQL的视图可更新标准中要求视图定义不能包含group by子句，但该视图定义包含了group by子句。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.4 根据**PostgreSQL数据库**的视图可更新标准，该视图是否为可更新视图，请说明理由。（1分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "不是。\n",
    "PostgreSQL数据库的视图可更新标准中要求视图定义不能包含group by子句，但该视图定义包含了group by子句。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time</th>\n",
       "        <th>position</th>\n",
       "        <th>username</th>\n",
       "        <th>carid</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-12 10:20:28</td>\n",
       "        <td>0101000020E610000023FD512FB3065E406D4C78D793483E40</td>\n",
       "        <td>Jack</td>\n",
       "        <td>101</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 5, 12, 10, 20, 28), '0101000020E610000023FD512FB3065E406D4C78D793483E40', 'Jack', '101')]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from track limit 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.5 为该视图创建触发器实现数据插入，roadID无需插入。（2分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE FUNCTION trigger_paris_insert() \n",
    "RETURNS trigger AS\n",
    "$$\n",
    "declare currenttime timestamp;\n",
    "usertext text;\n",
    "BEGIN\n",
    "    select time into currenttime from track where carid = new.carid order by time desc limit 1;\n",
    "    select track.username into usertext from track where carid = new.carid order by time desc limit 1;\n",
    "    currenttime = currenttime + interval '1 second';\n",
    "    insert into track(time, carID, position, username) values(currenttime,new.carid,new.position,usertext );\n",
    "    RETURN null;\n",
    "END;\n",
    "$$\n",
    "LANGUAGE 'plpgsql' VOLATILE;\n",
    "\n",
    "drop trigger if exists insertview on CurrentTrack;\n",
    "create trigger insertview\n",
    "instead of insert on CurrentTrack\n",
    "FOR EACH ROW\n",
    "EXECUTE PROCEDURE trigger_paris_insert();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql insert into CurrentTrack(carID, position) values('102', ST_GeomFromText('Point(120.10475310 30.28328588)', 4326));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time</th>\n",
       "        <th>st_astext</th>\n",
       "        <th>username</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-12 10:20:31</td>\n",
       "        <td>POINT(120.1047531 30.28328588)</td>\n",
       "        <td>David</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-12 10:20:30</td>\n",
       "        <td>POINT(120.1047810405 30.2831934779)</td>\n",
       "        <td>David</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-12 10:20:29</td>\n",
       "        <td>POINT(120.1047531 30.28328588)</td>\n",
       "        <td>David</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 5, 12, 10, 20, 31), 'POINT(120.1047531 30.28328588)', 'David'),\n",
       " (datetime.datetime(2020, 5, 12, 10, 20, 30), 'POINT(120.1047810405 30.2831934779)', 'David'),\n",
       " (datetime.datetime(2020, 5, 12, 10, 20, 29), 'POINT(120.1047531 30.28328588)', 'David')]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select time, ST_AsText(position), username from track where carID = '102' order by time desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. 路口方向判断（10分）\n",
    "\n",
    "在导航过程中，道路导航系统通常有语音提示，如“前方500米左转”，“前方500米有连续摄像，限速100，当前车速105，您已超速”等等。在开始导航时，系统将导航信息存储在guidepoints关系中。这里涉及到<a href=\"http://www.csdn.net/article/1970-01-01/2826221\" target=\"_blank\">引导角度</a>模块，也是Lecture 1中提到的四类空间分析：Measurements、Proximal、Topological和Directional中的方向分析。实现函数\n",
    "\n",
    "    void ST_GuideDirection(ids int[])\n",
    "\n",
    "函数输入导航路径上的道路编号，判断每个路口的前进方向，将引导信息存储在guidepoints关系中，guidepoints关系属性说明如下：\n",
    "* type表示引导点类型，1代表测速点，2代表路口转向，...\n",
    "* direction表示路口转向信息，0表示直行，1表示左转，2表示右转，3表示掉头，左转和右转判断是根据前进方向夹角大于等于45度\n",
    "* velocity表示测速点限速要求\n",
    "* geom表示引导点的位置\n",
    "* edgeid表示进入引导点或路口的道路编号\n",
    "\n",
    "方向判断方法：路口是至少有三条路相交的点，在路口找到3个点，P1在进入路口的道路上，P2是路口，P3在出路口的道路上，构建直线方程P1 $\\rightarrow$ P2：ax + by + c = 0，将P3的x和y带入该直线方程，通过数值是大于或小于0，判断P3点在直线的上方还是下方，即左和右，通过计算向量P2 - P1和P3 - P2的夹角，判断是否直行、左转或右转（不考虑掉头）。\n",
    "\n",
    "忽略道路内部的转弯提示，这些提示可以在建道路关系时生成，和导航路线基本无关，也就说走这条路都要转弯，没有岔路口，用户也不会走错。\n",
    "\n",
    "P1和P3点的初略选择：P1和P3为道路的另一节点。\n",
    "\n",
    "P1和P3点的精确选择：P1和P3到P2的距离在10米以上的最近道路内部顶点或另一节点。\n",
    "\n",
    "**P1和P3的选择：道路第2个点或者第N-1个点，假设道路有N个顶点，即1..N，作业使用这类选择方法。**\n",
    "\n",
    "PostgreSQL帮助文档给出了数组[ARRAY](https://www.postgresql.org/docs/current/static/functions-array.html)使用方法，如\n",
    "<pre><code>        id_cinema integer[] = ARRAY(select id from poi where name like '%影%' order by id);\n",
    "        For i in 1..array_length(id_cinema, 1)\n",
    "        LOOP\n",
    "            ......\n",
    "        END LOOP;\n",
    "</code></pre>\n",
    "\n",
    "注意：除了题目给出的紫金港到玉泉的最短路径的路口测试外，成绩还将通过其他路径进行评测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "drop table if exists guidepoints;\n",
    "CREATE Table guidepoints(id SERIAL primary key, type int,direction int,velocity float,geom geometry('POINT',4326),edgeid int);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "drop table if exists guidepoints_test;\n",
    "CREATE Table guidepoints_test(\n",
    "    type int,\n",
    "    direction int,\n",
    "    geom geometry('POINT',4326),\n",
    "    edgeid int,\n",
    "    edgeidn int,\n",
    "    geomr geometry,\n",
    "    geomNext geometry,\n",
    "    p1 geometry,\n",
    "    p2 geometry,\n",
    "    p3 geometry,\n",
    "    direct int,\n",
    "    dotr float,\n",
    "    cosr float,\n",
    "    liner float\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "create or replace function ST_GuideDirection(ids int[])\n",
    "     returns boolean\n",
    "as $$\n",
    "declare geomr geometry;\n",
    "        geomNext geometry;\n",
    "        p1 geometry;\n",
    "        p2 geometry;\n",
    "        p3 geometry;\n",
    "        p4 geometry;\n",
    "        direct int;\n",
    "        c int;\n",
    "        dotr float;\n",
    "        cosr float;\n",
    "        liner float;\n",
    "begin\n",
    "    delete from guidepoints_test;\n",
    "    For i in 1..(array_length(ids, 1)-1)\n",
    "    LOOP\n",
    "        select geom into geomr from road where id = ids[i];\n",
    "        select geom into geomNext from road where id = ids[i+1];\n",
    "        \n",
    "        p1 = 'point empty'::geometry;\n",
    "        \n",
    "        if st_astext(st_pointn(geomr,ST_NumPoints(geomr))) = st_astext(st_pointn(geomNext,ST_NumPoints(geomNext))) then\n",
    "            p1 = st_pointn(geomr,ST_NumPoints(geomr)-1);\n",
    "            p2 = st_pointn(geomr,ST_NumPoints(geomr));\n",
    "            p3 = st_pointn(geomNext,ST_NumPoints(geomNext)-1);\n",
    "        end if;\n",
    "        if st_astext(st_pointn(geomr,1)) = st_astext(st_pointn(geomNext,1)) then\n",
    "            p1 = st_pointn(geomr,2);\n",
    "            p2 = st_pointn(geomr,1);\n",
    "            p3 = st_pointn(geomNext,2);\n",
    "        end if;\n",
    "        if st_astext(st_pointn(geomr,1)) = st_astext(st_pointn(geomNext,ST_NumPoints(geomNext))) then\n",
    "            p1 = st_pointn(geomr,2);\n",
    "            p2 = st_pointn(geomr,1);\n",
    "            p3 = st_pointn(geomNext,ST_NumPoints(geomNext)-1);\n",
    "        end if;\n",
    "        if st_astext(st_pointn(geomr,ST_NumPoints(geomr))) = st_astext(st_pointn(geomNext,1)) then\n",
    "            p1 = st_pointn(geomr,ST_NumPoints(geomr)-1);\n",
    "            p2 = st_pointn(geomr,ST_NumPoints(geomr));\n",
    "            p3 = st_pointn(geomNext,2);\n",
    "        end if;\n",
    "        \n",
    "        select count(*) into c from\n",
    "        (select id from road where st_astext(st_pointn(geom,ST_NumPoints(geom))) = st_astext(p2) \n",
    "         or st_astext(st_pointn(geom,1)) = st_astext(p2) group by id) ct;\n",
    "        \n",
    "        dotr = dot(st_x(p2)-st_x(p1),st_y(p2)-st_y(p1),st_x(p3)-st_x(p2),st_y(p3)-st_y(p2));\n",
    "        cosr = ST_P2PDistance(st_x(p2),st_y(p2),st_x(p1),st_y(p1))*ST_P2PDistance(st_x(p2),st_y(p2),st_x(p3),st_y(p3));\n",
    "        liner = (st_y(p2)-st_y(p1))*st_x(p3)-(st_x(p2)-st_x(p1))*st_y(p3)+st_x(p2)*st_y(p1)-st_x(p1)*st_y(p2);\n",
    "\n",
    "        --insert into guidepoints_test(type,direction,geom,edgeid,edgeidn,p1,p2,p3,dotr,cosr,liner) values(1,direct,p2,ids[i],ids[i+1],p1,p2,p3,dotr,cosr,liner);\n",
    "\n",
    "        \n",
    "        if c <= 2 then\n",
    "            continue;\n",
    "        end if;\n",
    "        --if p1 = 'point empty'::geometry then\n",
    "        --    return False;\n",
    "        --end if;\n",
    "        --if p1 = p2 or p2 = p3 then\n",
    "        --    return False;\n",
    "        --end if;\n",
    "        if liner > 0 then\n",
    "            if dotr >  0.70710678118655*cosr then\n",
    "                direct = 0;\n",
    "            else\n",
    "                direct = 2;\n",
    "            end if;\n",
    "        else\n",
    "            if dotr >  0.70710678118655*cosr then\n",
    "                direct = 0;\n",
    "            else\n",
    "                direct = 1;\n",
    "            end if;\n",
    "        end if;\n",
    "        insert into guidepoints(type,direction,geom,edgeid) values(2,direct,p2,ids[i]);\n",
    "        END LOOP;\n",
    "    return True;\n",
    "end;\n",
    "$$ language plpgsql;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据下图中所示网络(数字表示对应边的ID)，进行简单测试，可自行增加部分测试<img src=\"SimpleNetwork.png\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "3 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "3 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "3 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "4 / 4 tests are passed\n"
     ]
    }
   ],
   "source": [
    "routes = [\"[16,17913,22811,7273,7268]\",\"[7272,7273,7274,7275]\",\"[7275,7274,22811,17912]\",\"[7267,23300,7277,7271,7274,7273]\"]\n",
    "turns  = [\"[1, 2, 1]\",\"[0, 0, 0]\",\"[0, 2, 1]\",\"[2, 1, 2, 1, 0]\"]\n",
    "\n",
    "template = \"\"\"\n",
    "delete from guidepoints;\n",
    "select ST_GuideDirection(array%s);\n",
    "select Array(select direction from guidepoints),array%s,Array(select direction from guidepoints) = array%s as result\n",
    "\"\"\"\n",
    "\n",
    "passedTests = len(routes)\n",
    "for i in range(len(routes)):\n",
    "    route  = routes[i]\n",
    "    turn   = turns[i]\n",
    "    query  = template % (route, turn, turn)\n",
    "    result = %sql $query\n",
    "    if result[0][2] is False:\n",
    "        passedTests -= 1\n",
    "        print('Array '+ route + ' GuideDirection test failed. Your result is '+ str(result[0][0]) + ', correct result is '+ turn + '\\n')\n",
    "        \n",
    "print(str(passedTests) + ' / ' + str(len(routes)) + \" tests are passed\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "生成从紫金港（13083）到玉泉（5846）的最短驾驶距离对应的路线的路口引导信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>st_guidedirection</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>True</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(True,)]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql delete from guidepoints\n",
    "\n",
    "query = \"\"\"\n",
    "select ST_GuideDirection(\n",
    "Array(select edge::int \n",
    "from pgr_dijkstra('select id, source, target,len as cost from road', \n",
    "                  13083, \n",
    "                  5846, false)));\n",
    "\"\"\"\n",
    "\n",
    "%sql $query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "与referenceguidepoints关系中的参考答案进行比较"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "268 rows affected.\n",
      "68 rows affected.\n",
      "转向点判断正确\n"
     ]
    }
   ],
   "source": [
    "def getDict(query, flg = 1):\n",
    "    result = %sql $query\n",
    "    res = {}\n",
    "    length = len(result)\n",
    "    for i in range(0, length):\n",
    "        if flg == 1:\n",
    "            res[result[i]['geom']] = result[i]['direction']\n",
    "        else:\n",
    "            res[str(result[i]['time']) + \",  \" +  result[i]['carid']] = result[i]['message']\n",
    "    return res\n",
    "\n",
    "def getPoint(geom):\n",
    "    query = \"select st_astext('%s'::geometry)\" % (geom)\n",
    "    res = %sql $query\n",
    "    return str(res[0][0])\n",
    "\n",
    "def compareResult(res1, res2, cat = 1):\n",
    "    flg = 1\n",
    "    turn = len(res2) - len(res1)\n",
    "    for attr in res2:\n",
    "        if attr in res1:\n",
    "            if res1[attr] != res2[attr]:\n",
    "                if cat == 1:\n",
    "                    geom = getPoint(attr)\n",
    "                    print(\"点(%s)转向判断错误\" % (geom))\n",
    "                else:\n",
    "                    print(\"(\", attr, \")消息提示错误\")\n",
    "                flg = 0\n",
    "        else:\n",
    "            if cat == 1:\n",
    "                geom = getPoint(attr)\n",
    "                print(\"点(%s)不是转向点\" %(geom))\n",
    "            else:\n",
    "                print(attr, \"不需要消息提示\")\n",
    "            turn = turn - 1\n",
    "            flg = 0\n",
    "    if turn < 0:\n",
    "        print(\"遗漏了(%d)个\" % (-turn), (\"转向点\" if cat == 1 else \"消息提示\"), \":\")\n",
    "        for attr in res1:\n",
    "            if not attr in res2:\n",
    "                if cat == 1:\n",
    "                    geom = getPoint(attr)\n",
    "                    print(\"点(%s)\" % (geom))\n",
    "                else:\n",
    "                    print(\"(\", attr, \")\")\n",
    "        flg = 0\n",
    "    \n",
    "    if flg == 1:\n",
    "        print(\"转向点判断正确\" if cat == 1 else \"消息提示正确\")\n",
    "\n",
    "        \n",
    "res1 = getDict(\"select direction, geom from referenceguidepoints\")\n",
    "res2 = getDict(\"select direction, geom from guidepoints where type = 2\")\n",
    "compareResult(res1, res2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 976,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "142 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 976,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists routezy;\n",
    "select id as gid, id as name ,geom into routezy from road,\n",
    "(select edge \n",
    "from pgr_dijkstra('select id, source, target,len as cost from road', \n",
    "                  13083, \n",
    "                  5846, false)) e where id = edge;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 977,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "142 rows affected.\n",
      "68 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div id='map1' style=\"width: 1000px; height: 400px\"></div>\n",
       "    <link rel=\"stylesheet\" href=\"tools/leaflet.css\">\n",
       "    <link rel=\"stylesheet\" href=\"tools/map.css\">\n",
       "        <script src=\"tools/leaflet.js\" type=\"text/javascript\"></script>\n",
       "        <script src=\"jsonData/map1.json\" type=\"text/javascript\"></script>\n",
       "        <script src=\"tools/wkx.js\" type=\"text/javascript\"></script>\n",
       "        <script src=\"tools/geom_display.js\" type=\"text/javascript\"></script>\n",
       "        <script src=\"tools/d3.min.js\" type=\"text/javascript\"></script>\n",
       "         <script type=\"text/javascript\"> \n",
       "        (new GeoMap('map1' ,13,0,0)).display();</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 路线\n",
    "query = \"\"\"\n",
    "select id as gid, id as name ,geom from road,\n",
    "(select edge \n",
    "from pgr_dijkstra('select id, source, target,len as cost from road', \n",
    "                  13083, \n",
    "                  5846, false)) e where id = edge\n",
    "\"\"\"\n",
    "\n",
    "result1 = %sql $query\n",
    "\n",
    "result2 = %sql select id as gid, '' || direction as name, geom from guidepoints\n",
    "\n",
    "#result3 = %sql select 2 as gid, ' ' as name,'POINT(120.115014524806 30.2743784852638)'::geometry as geom\n",
    "\n",
    "display([result1,result2], \"map1\", 13)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在QGIS中连接hw7数据库，通过QGIS中DB Manager工具将紫金港（13083）到玉泉（5846）的最短驾驶距离对应的路线以及对应的路口引导信息加载到图层中，并根据转向对引导点图层进行Categorized符号化，截图保存为学号.jpg，与本文件同一目录，修改下面的Path.png为你的学号.jpg，Shift+Enter能正确展示QGIS截图。可能由于浏览器图片缓存原因，修改后不能立即显示新图片，重新打开jupyter notebook验证图片是否正确显示。<img src=\"3180102760.jpg\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. 测速点提示（10分）\n",
    "\n",
    "在导航过程中，导航系统能够提醒司机前方是否有测速点。测速点信息在guidepoints关系中，包括限速信息和测速点位置。在实际行驶中，导航系统将自动获取车所在的位置，将当前时间和车的位置等信息存储在track关系中，track关系属性说明如下：\n",
    "* time timestamp default CURRENT_TIMESTAMP\n",
    "* userName text default SESSION_USER\n",
    "* position geometry(POINT, 4326)\n",
    "* carID text\n",
    "\n",
    "创建触发器speedNotifyTrigger，模拟道路导航系统的测速点提示。为避免重复提示，仅在测速点100米处，即前一时间刻的位置大于100米，当前时刻的位置小于等于100米，根据前一时间刻和当前时间刻的时间和位置，简单估算车速（取整），根据车速将提示内容插入到notifymessage(time, driver, message)中(模拟语音提示)。这里涉及<a href=\"http://www.csdn.net/article/1970-01-01/2826221\" target=\"_blank\">引导点</a>模块。\n",
    "<table>\n",
    " <tr><th>车速v (公里/小时)</th>    <th>提示(x为当前位置到测速点距离，y为该道路的最大速度)</th></tr>\n",
    "    <tr><td>${v \\leqslant y\\ast 0.9}$    </td><td>前方限速y</td></tr>\n",
    "    <tr><td>${y\\ast 0.9 <  v \\leqslant y}$</td><td>前方限速y，当前车速v</td></tr>\n",
    "    <tr><td>${v >  y}$</td><td>前方限速y，当前车速v，您已超速</td></tr>\n",
    "</table>\n",
    "\n",
    "notifymessage关系属性说明如下：\n",
    "* time timestamp default CURRENT_TIMESTAMP,\n",
    "* carID text\n",
    "* message text\n",
    "\n",
    "注意：除了题目给出的紫金港到玉泉的最短路径的测速点外，成绩还将通过其他测速点进行评测\n",
    "\n",
    "导入从紫金港（13083）到玉泉（5846）的最短驾驶距离对应的路线上的测速点信息。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "68 rows affected.\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists track cascade;\n",
    "drop table if exists notifymessage;\n",
    "\n",
    "create table track(\n",
    "    time timestamp default CURRENT_TIMESTAMP,\n",
    "    position geometry(POINT, 4326),\n",
    "    userName text default SESSION_USER,\n",
    "    carID text\n",
    ");\n",
    "\n",
    "create table notifymessage(\n",
    "    time timestamp default CURRENT_TIMESTAMP,\n",
    "    carID text,\n",
    "    message text\n",
    ");\n",
    "delete from guidepoints;\n",
    "copy guidepoints(type, direction,velocity, geom, edgeid) from 'D:\\works\\GIS\\geodatabase\\hw7\\guidepoints.txt' delimiter '#';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 958,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "142 rows affected.\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div id='map2' style=\"width: 1000px; height: 400px\"></div>\n",
       "    <link rel=\"stylesheet\" href=\"tools/leaflet.css\">\n",
       "    <link rel=\"stylesheet\" href=\"tools/map.css\">\n",
       "        <script src=\"tools/leaflet.js\" type=\"text/javascript\"></script>\n",
       "        <script src=\"jsonData/map2.json\" type=\"text/javascript\"></script>\n",
       "        <script src=\"tools/wkx.js\" type=\"text/javascript\"></script>\n",
       "        <script src=\"tools/geom_display.js\" type=\"text/javascript\"></script>\n",
       "        <script src=\"tools/d3.min.js\" type=\"text/javascript\"></script>\n",
       "         <script type=\"text/javascript\"> \n",
       "        (new GeoMap('map2' ,13,0,0)).display();</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 路线\n",
    "query = \"\"\"\n",
    "select id as gid, id as name ,geom from road,\n",
    "(select edge \n",
    "from pgr_dijkstra('select id, source, target,len as cost from road', \n",
    "                  13083, \n",
    "                  5846, false)) e where id = edge\n",
    "\"\"\"\n",
    "\n",
    "result1 = %sql $query\n",
    "\n",
    "#插入测速点信息\n",
    "result2 = %sql select velocity as name, edgeid as gid, geom from guidepoints;\n",
    "\n",
    "display([result1, result2], \"map2\", 13)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 959,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 959,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE OR REPLACE FUNCTION trigger_paris_insert() \n",
    "RETURNS trigger AS\n",
    "$$\n",
    "declare currentdist float;\n",
    "        oldtime timestamp;\n",
    "        oldpos geometry;\n",
    "        movedistance float;\n",
    "        pointgeom geometry;\n",
    "        timeinterval float;\n",
    "        v float;\n",
    "        vlimit float;\n",
    "BEGIN\n",
    "\n",
    "    select time into oldtime from track where carid = new.carid order by time desc limit 1;\n",
    "    select position into oldpos from track where carid = new.carid order by time desc limit 1;\n",
    "    if oldtime = null then\n",
    "        return new;\n",
    "    end if;\n",
    "    \n",
    "    currentdist = -1;\n",
    "    select st_distance(new.position::geography,geom::geography) dis into currentdist \n",
    "    from guidepoints where st_distance(new.position::geography,geom::geography) < 100 and\n",
    "    st_distance(oldpos::geography,geom::geography) > 100 order by dis limit 1;\n",
    "    \n",
    "    select geom into pointgeom \n",
    "    from guidepoints where st_distance(new.position::geography,geom::geography) < 100 and \n",
    "    st_distance(oldpos::geography,geom::geography) > 100 \n",
    "    and st_distance(new.position::geography,geom::geography) = currentdist limit 1;\n",
    "    \n",
    "    select velocity into vlimit \n",
    "    from guidepoints where st_distance(new.position::geography,geom::geography) < 100 and \n",
    "    st_distance(oldpos::geography,geom::geography) > 100 \n",
    "    and st_distance(new.position::geography,geom::geography) = currentdist limit 1;\n",
    "    \n",
    "    movedistance = st_distance(oldpos::geography,pointgeom::geography) - currentdist;\n",
    "    timeinterval = EXTRACT(EPOCH FROM (new.time - oldtime));\n",
    "    v = movedistance/timeinterval * 3.6;\n",
    "    \n",
    "    if currentdist > 0 then\n",
    "        if v <= 0.9*vlimit then\n",
    "            insert into notifymessage(time,carid,message) \n",
    "            values(NEW.time,new.carid, '前方限速'||vlimit||'km/h');\n",
    "        end if;\n",
    "        if v > 0.9*vlimit and v<=vlimit then\n",
    "            insert into notifymessage(time,carid,message) \n",
    "            values(NEW.time,new.carid, '前方限速'||vlimit||'km/h,当前车速'||round(v::numeric,0)||'km/h');\n",
    "        end if;\n",
    "        if  v>vlimit then\n",
    "            insert into notifymessage(time,carid,message) \n",
    "            values(NEW.time,new.carid, '前方限速'||vlimit||'km/h,当前车速'||round(v::numeric,0)||'km/h,您已超速');\n",
    "        end if;\n",
    "    end if;\n",
    "    RETURN new;\n",
    "END;\n",
    "$$\n",
    "LANGUAGE 'plpgsql' VOLATILE;\n",
    "\n",
    "drop trigger if exists speedNotifyTrigger on track;\n",
    "create trigger speedNotifyTrigger\n",
    "before insert on track\n",
    "For Each Row\n",
    "EXECUTE PROCEDURE trigger_paris_insert();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 960,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>date_part</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>82.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(82.0,)]"
      ]
     },
     "execution_count": 960,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select st_distance(ST_GeomFromText('point(120.104686575 30.283505885)',4326)::geography ,geom::geography ) from guidepoints;\n",
    "select '2020-05-25 10:20:50'::timestamp - '2020-05-25 10:20:28'::timestamp;\n",
    "select EXTRACT(EPOCH FROM ('2020-05-25 10:21:50'::timestamp - '2020-05-25 10:20:28'::timestamp))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 961,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 961,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "delete from notifymessage;\n",
    "\n",
    "insert into track values('2020-05-25 10:20:28', ST_GeomFromText('point(120.104686575 30.283505885)',4326), 'Jack' , '101');\n",
    "insert into track values('2020-05-25 10:20:29', ST_GeomFromText('point(120.10475310 30.28328588)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:30', ST_GeomFromText('point(120.104819625 30.283065875)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:31', ST_GeomFromText('point(120.10488615 30.28284587)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:32', ST_GeomFromText('point(120.104952675 30.282625865)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:33', ST_GeomFromText('point(120.104819625 30.283065875)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:34', ST_GeomFromText('point(120.104979285 30.282537863)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:35', ST_GeomFromText('point(120.1049992425 30.2824718615)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:36', ST_GeomFromText('point(120.10501920 30.28240586)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:37', ST_GeomFromText('point(120.105045810 30.282317858)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:38', ST_GeomFromText('point(120.105085725 30.282185855)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:39', ST_GeomFromText('point(120.105125640 30.282053852)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:40', ST_GeomFromText('point(120.105178860 30.281877848)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:41', ST_GeomFromText('point(120.105218775 30.281745845)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:42', ST_GeomFromText('point(120.105298605 30.281481839)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:43', ST_GeomFromText('point(120.105378435 30.281217833)',4326), 'Jack', '101');\n",
    "insert into track values('2020-05-25 10:20:29', ST_GeomFromText('point(120.10475310 30.28328588)',4326), 'David', '102');\n",
    "insert into track values('2020-05-25 10:20:30', ST_GeomFromText('point(120.1047810405 30.2831934779)',4326), 'David', '102');\n",
    "insert into track values('2020-05-25 10:20:29', ST_GeomFromText('point(120.10475310 30.28328588)',4326), 'Tom', '103');\n",
    "insert into track values('2020-05-25 10:20:30', ST_GeomFromText('point(120.1047770490 30.2832066782)',4326), 'Tom', '103');\n",
    "insert into track values('2020-05-25 10:20:29', ST_GeomFromText('point(120.103880996283 30.2860733641809)',4326), 'Sally', '104');\n",
    "insert into track values('2020-05-25 10:20:30', ST_GeomFromText('point(120.1039895370264 30.28572229134472)',4326), 'Sally', '104');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 962,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time</th>\n",
       "        <th>carid</th>\n",
       "        <th>message</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>101</td>\n",
       "        <td>前方限速40km/h,当前车速91km/h,您已超速</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>102</td>\n",
       "        <td>前方限速40km/h,当前车速38km/h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>103</td>\n",
       "        <td>前方限速40km/h</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 5, 25, 10, 20, 30), '101', '前方限速40km/h,当前车速91km/h,您已超速'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '102', '前方限速40km/h,当前车速38km/h'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '103', '前方限速40km/h')]"
      ]
     },
     "execution_count": 962,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from notifymessage;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 963,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>time</th>\n",
       "        <th>carid</th>\n",
       "        <th>message</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>101</td>\n",
       "        <td>前方限速40km/h,当前车速91km/h,您已超速</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>102</td>\n",
       "        <td>前方限速40km/h,当前车速38km/h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>103</td>\n",
       "        <td>前方限速40km/h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>101</td>\n",
       "        <td>前方限速40km/h,当前车速91km/h,您已超速</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>102</td>\n",
       "        <td>前方限速40km/h,当前车速38km/h</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2020-05-25 10:20:30</td>\n",
       "        <td>103</td>\n",
       "        <td>前方限速40km/h</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2020, 5, 25, 10, 20, 30), '101', '前方限速40km/h,当前车速91km/h,您已超速'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '102', '前方限速40km/h,当前车速38km/h'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '103', '前方限速40km/h'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '101', '前方限速40km/h,当前车速91km/h,您已超速'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '102', '前方限速40km/h,当前车速38km/h'),\n",
       " (datetime.datetime(2020, 5, 25, 10, 20, 30), '103', '前方限速40km/h')]"
      ]
     },
     "execution_count": 963,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select * from referencenotifymessage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "与referencenotifymessage关系中的参考答案进行比较"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 964,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 rows affected.\n",
      "3 rows affected.\n",
      "消息提示正确\n"
     ]
    }
   ],
   "source": [
    "res1 = getDict(\"select * from referencenotifymessage\", 2)\n",
    "res2 = getDict(\"select * from notifymessage\", 2)\n",
    "compareResult(res1, res2, cat = 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "路口转向提示也可以通过类似的方式实现。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 作业感想（6分）\n",
    "\n",
    "这是地理空间数据库课程的最后一个个人作业，填写本次作业感想有加分哦<br/>\n",
    "\n",
    "对课程内容有什么建议，比如希望增加哪些内容（如果没学过数据库系统原理大类课程，哪些内容不容易理解，需要详细介绍），少讲哪些内容（太难或太简单了，比如空间索引实现、PostgreSQL服务器编程、pgRouting基于几何对象模型的网络模型构建等）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "感觉空间索引实现那一部分比较难理解这样；\n",
    "感觉可以多增加一些数据库底层索引实现编程的内容，然后平常的测试可以稍微少一点，作业和实践占比加大；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对课程作业有什么建议，比如作业内容和组织结构，作业难易程度，postgresql和jupyter notebook等工具的使用等等。作业5和7是关于空间数据库具体实现，是否有必要，是否有补充的测试用例，是否需要增加数据库应用开发实现，特别是事务处理？"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "作业难度还行；\n",
    "作业5和7是关于空间数据库具体实现，感觉5还是比较有必要的，也许7可以稍微缩减一点；有补充新的测试用例；\n",
    "希望可以增加一些数据库应用开发实现，可能测试并不需要这么多；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对课程的其他建议"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "希望可以平时分占比大一点；"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
